import pandas as pd
import os
# 读十个Excel表格内容
# 先将路径组成列表，遍历出每个路径
#再遍历每个文件，读取10个表格内容


def average_score(path):
    df = pd.read_excel(path, index_col='学号')
    df['总分'] = df['语文'] + df['数学'] + df['英语']  # 三列求和相加
    # df['总分'] = df[['省份', '语文', '数学', '英语']].sum(axis=1)#此处有提示
    df1 = df[['省份', '语文', '数学', '英语', '总分']]
    average_scores = df1.groupby('省份').agg(['mean'])  # 以省份分组三科平均分
    average_scores_df = average_scores.round(2)
    with pd.ExcelWriter(path, mode='a', if_sheet_exists='replace') as writer:  # 追加一个sheet
        average_scores_df.to_excel(writer, sheet_name='各省份平均分')


if __name__ == '__main__':
    path1 = r'D:\ITAudit\pandas_excel\pc'
    # average_score(r"D:\ITAudit\pandas_excel\pc\Stu_new_1score_list.xlsx")
    excels = [fname for fname in os.listdir(path1)]
    for excel_path in excels:
        final_path = path1 + f'\\{excel_path}'
        print(final_path)
        average_score(final_path)
    # average_score("D:\ITAudit\pandas_excel\pc\Stu_new_2score_list.xlsx")


